# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import jieba
import pymysql
import redis


class MyjdtestPipeline(object):
    def __init__(self):
        self.db=pymysql.connect('139.9.129.134','zjw','Gem1234@','jdtest',charset='utf8')
        self.cursor=self.db.cursor()

        self.sql = """select `p_number` from phones_phoneinfo"""
        self.cursor.execute(self.sql)
        self.rows = self.cursor.fetchall()

        self.redisClient=redis.Redis(host='139.9.129.134',port=6379,db=0,password='Gem1234@')
    def process_item(self, item, spider):

        if item['p_number'] in self.rows:
            # 更新phones_phoneinfo
            updatesql="""update phones_phoneinfo set `p_price`=('%s') where `p_number`=item['p_number']"""%(item['p_price'])
            self.cursor.execute(updatesql)
            self.db.commit()
            # , 插入价格表
            price = item['p_price']
            number = item['p_number']
            p_time = item['p_updateTime']
            sqlprice = "insert into phones_pricerecord(`p_number`,`p_time`,`p_price`)values ('%s','%s','%s')" % (
            number, p_time, price)
            try:
                self.cursor.execute(sqlprice)
                self.db.commit()
            except:
                self.db.ping(reconnect=True)
                self.cursor.execute(sqlprice)
                self.db.commit()


        else:
            # 插入数据到phones_phoneinfo，价格表，参数表，


            p_title = item['p_title']
            p_brand = item['p_brand']
            p_img = item['p_img']
            p_price = item['p_price']
            p_number = item['p_number']
            p_href = item['p_href']
            p_comment_num = item['p_comment_num']
            p_updateTime = item['p_updateTime']
            p_store = item['p_store']
            p_plate=item['p_plate']
            sql="""insert into phones_phoneinfo(`p_title`,`p_brand`,`p_img`,`p_price`,`p_number`,`p_href`,`p_comment_num`,`p_updateTime`,`p_store`,`p_plate`) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"""%(p_title,p_brand,p_img,p_price,p_number,p_href,p_comment_num,p_updateTime,p_store,p_plate)
            try:
                self.cursor.execute(sql)
                self.db.commit()
            except:
                self.db.ping()
                self.cursor.execute(sql)
                self.db.commit()

            last_id=self.cursor.lastrowid

            # self.redisClient.sadd(p_title,last_id)
            words=jieba.cut(p_title)
            for word in words:
                self.redisClient.sadd(word,last_id)#词和主键关联


            d_number = item['d_number']
            d_weight = item['d_weight']
            d_cpuType = item['d_cpuType']
            d_ram = item['d_ram']
            d_rom = item['d_rom']
            d_bcamera = item['d_bcamera']
            d_fcamera = item['d_fcamera']
            d_mainScreen = item['d_mainScreen']
            d_resolution = item['d_resolution']
            d_os = item['d_os']
            d_plate=item['d_plate']
            sqlParam="insert into phones_detailparam(`d_number`,`d_weight`,`d_cpuType`,`d_ram`,`d_rom`,`d_bcamera`,`d_fcamera`,`d_mainScreen`,`d_resolution`,`d_os`,`d_plate`) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(d_number,d_weight,d_cpuType,d_ram,d_rom,d_bcamera,d_fcamera,d_mainScreen,d_resolution,d_os,d_plate)
            try:
                self.cursor.execute(sqlParam)
                self.db.commit()
            except:
                self.db.ping()
                self.cursor.execute(sqlParam)
                self.db.commit()


            # 价格表的数据信息
            price=item['p_price']
            number=item['p_number']
            p_time=item['p_updateTime']
            sqlprice="insert into phones_pricerecord(`p_number`,`p_time`,`p_price`)values ('%s','%s','%s')"%(number,p_time,price)
            try:
                self.cursor.execute(sqlprice)
                self.db.commit()
            except:
                self.db.ping()
                self.cursor.execute(sqlprice)
                self.db.commit()











            return item
